package com.nf.practice.exportExcel;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.HttpResponse;
import org.apache.http.HttpStatus;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.junit.jupiter.api.Test;
import org.springframework.util.CollectionUtils;
import java.io.File;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

// 需求1：
// 分别把案件【诉讼标的】、【执行标的】这两个字段做加工汇总，导出excel给到贷后这边
//        诉讼标的是，案件类型为一审或二审的诉讼标的金额加总
//        执行标的是，案件类型为首次执行的执行标的金额加总
// 需求2：需求1的基础上迭代
/*需要输出字段：汇总后的
        【诉讼标的】、
        【执行标的】，
        【广州市网商小额贷款有限责任公司-执行标的】，
        【广州市网商小额贷款有限责任公司-诉讼标的】，
        【广州网商融资担保有限公司-执行标的】、
        【广州网商融资担保有限公司-诉讼标的】、
        【广州网商商业保理有限责任公司-执行标的】、
        【广州网商商业保理有限责任公司-诉讼标的】

        其中：
        诉讼标的是，案件类型为一审或二审的诉讼标的金额加总
        执行标的是，案件类型为首次执行的执行标的金额加总

        本次新增字段说明如下：
        广州市网商小额贷款有限责任公司-执行标的，
        案件类型为首次执行，
        当事人信息——诉讼地位：申请执行人 为广州市网商小额贷款有限责任公司的执行标的金额加总

        广州市网商小额贷款有限责任公司-诉讼标的，
        案件类型为一审或二审，当事人信息——诉讼地位：原告/上诉人
        为广州市网商小额贷款有限责任公司的诉讼标的金额加总

        其他几个主体逻辑同上
*/
/*
诉讼地位: n_ssdw
申请执行标的金额: n_sqzxbdje
*/
public class SfPersonCaseAmtExport {

//    private final String dmsUrl = "xxxx";
    private final String dmsUrl = "http://172.16.32.15:31306/service";

    @Test
    public void batchSfPersonCaseMoneyExport() {
        Long begin = System.currentTimeMillis();
        String fileName = "E:/司法0220.xlsx";

        // 读取到的数据
        List<SfFile> list = EasyExcel.read(fileName)
                .head(SfFile.class)
                .sheet()
                .doReadSync();

        List<SfPersonCaseAmt> dataList = new ArrayList<>();

        List<String> idNoList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            String appNo = list.get(i).getAppNo();
            String name = list.get(i).getName();
            String idNo = list.get(i).getIdNo();
            System.out.println(appNo);
            System.out.println(idNo);
            System.out.println(name);
            if (StringUtils.isBlank(appNo) || StringUtils.isBlank(idNo) || StringUtils.isBlank(name)) {
                System.out.println("单号或者身份证号或者姓名==>为空:" + appNo+"~"+idNo+"~"+name);
                continue;
            }
            if (StringUtils.isNotBlank(idNo) && idNo.length() < 15) {
                System.out.println("idNo不合规:" + idNo);
                continue;
            }
            if (!CollectionUtils.isEmpty(idNoList) && idNoList.contains(idNo)) {
                System.out.println("重复项：name：" + name + "idNo: " + idNo);
                continue;
            }
            if (StringUtils.isNotBlank(idNo)) {
                idNoList.add(idNo);
            }
            try {
                // 2、调用dms
                // 公开模型
                JSONObject ss01EntJsonRes = getSourceData(appNo, idNo, name, "ENTOUT");
                JSONObject ss01EntJsonResData = ss01EntJsonRes.getJSONObject("data");
                if (ss01EntJsonResData == null || ss01EntJsonResData.isEmpty()) continue;
                JSONObject ss01EntJson = ss01EntJsonResData.getJSONObject("data");
                if (ss01EntJson == null || ss01EntJson.isEmpty()) continue;
//                // 刑事案件
//                BigDecimal criminalAmt = getAmtTotal(ss01EntJson, "criminal");
//                BigDecimal bankruptAmt = getAmtTotal(ss01EntJson, "bankrupt");
//                BigDecimal preservationAmt = getAmtTotal(ss01EntJson, "preservation");
//                BigDecimal civilAmt = getAmtTotal(ss01EntJson, "civil");
//                BigDecimal administrativeAmt = getAmtTotal(ss01EntJson, "administrative");
//                // 诉讼
//                BigDecimal litigationAmt = criminalAmt.add(bankruptAmt).add(preservationAmt).add(civilAmt).add(administrativeAmt);
//                // 执行案件
//                BigDecimal implementAmt = getAmtTotal(ss01EntJson, "implement");

                // 民事案件
                Map<String,BigDecimal> civilRes = getAmtTotal(ss01EntJson, "civil");
                // 民事诉讼标的金额
                BigDecimal litigationAmt = civilRes.get("litigationAmt");
                // 民事诉讼标的
                BigDecimal wsLoanLitiAmt = civilRes.get("wsLoanLitiAmt");
                BigDecimal wsFinGuaLitiAmt = civilRes.get("wsFinGuaLitiAmt");
                BigDecimal wsBusGuaLitiAmt = civilRes.get("wsBusGuaLitiAmt");

                // 行政案件
                Map<String,BigDecimal> administrativeRes = getAmtTotal(ss01EntJson, "administrative");
                // 行政诉讼标的金额
                BigDecimal admLitigationAmt = administrativeRes.get("litigationAmt");
                // 行政诉讼标的
                BigDecimal admWsLoanLitiAmt = administrativeRes.get("wsLoanLitiAmt");
                BigDecimal admWsFinGuaLitiAmt = administrativeRes.get("wsFinGuaLitiAmt");
                BigDecimal admWsBusGuaLitiAmt = administrativeRes.get("wsBusGuaLitiAmt");

                // 执行案件
                Map<String,BigDecimal> implRes = getAmtTotal(ss01EntJson, "implement");
                BigDecimal implementAmt = implRes.get("implementAmt");
                // 执行标的
                BigDecimal wsLoanImplAmt = implRes.get("wsLoanImplAmt");
                BigDecimal wsFinGuaImplAmt = implRes.get("wsFinGuaImplAmt");
                BigDecimal wsBusGuaImplAmt = implRes.get("wsBusGuaImplAmt");


                SfPersonCaseAmt data = new SfPersonCaseAmt();
                data.setAppNo(appNo).setIdNo(idNo).setName(name)
                        .setLitigationAmt(litigationAmt.add(admLitigationAmt)).setWsLoanLitiAmt(wsLoanLitiAmt.add(admWsLoanLitiAmt))
                        .setWsFinGuaLitiAmt(wsFinGuaLitiAmt.add(admWsFinGuaLitiAmt)).setWsBusGuaLitiAmt(wsBusGuaLitiAmt.add(admWsBusGuaLitiAmt))
                        .setImplementAmt(implementAmt).setWsLoanImplAmt(wsLoanImplAmt)
                        .setWsFinGuaImplAmt(wsFinGuaImplAmt).setWsBusGuaImplAmt(wsBusGuaImplAmt)
                ;
                dataList.add(data);
                System.out.println("========>" + idNo + "--" + name + "成功========>");
            } catch (Exception e) {
                System.out.println(idNo + "--" + name + "异常====!!!" + e.getMessage());
            }
        }
        // 使用 Java 8 Stream API 进行排序
//        dataList.sort(Comparator
//                .comparing(SfPersonCaseAmt::getLitigationAmt, Comparator.reverseOrder()) // 按 litigationAmt 降序
//                .thenComparing(SfPersonCaseAmt::getImplementAmt, Comparator.reverseOrder()) // 按 implementAmt 降序
//        );
        ExcelWriter writer = ExcelUtil.getWriter(new File("E:/司法0220导出.xlsx"));
        if(!CollectionUtils.isEmpty(dataList)){
            writer.setSheet(0).write(dataList);
            writer.flush();
        }
        System.out.println("========>导出完成========>计算耗时：" + (System.currentTimeMillis() - begin) + "ms");
    }

    public Map<String, BigDecimal> getAmtTotal(JSONObject ss01EntJson, String caseType) {
        Map<String, BigDecimal> result = new HashMap<>();
//        BigDecimal amtRes = BigDecimal.ZERO;
        BigDecimal implementAmt = BigDecimal.ZERO;
        BigDecimal litigationAmt = BigDecimal.ZERO;

        BigDecimal wsLoanImplAmt = BigDecimal.ZERO;
        BigDecimal wsLoanLitiAmt = BigDecimal.ZERO;

        BigDecimal wsFinGuaImplAmt = BigDecimal.ZERO;
        BigDecimal wsFinGuaLitiAmt = BigDecimal.ZERO;

        BigDecimal wsBusGuaImplAmt = BigDecimal.ZERO;
        BigDecimal wsBusGuaLitiAmt = BigDecimal.ZERO;

        JSONObject caseJsonData = Optional.ofNullable(ss01EntJson.getJSONObject(caseType)).orElse(new JSONObject());
        JSONArray caseJsonArr = Optional.ofNullable(caseJsonData.getJSONArray("cases")).orElse(new JSONArray());
        for (int i = 0; i < caseJsonArr.size();i++) {
            JSONObject caseJsonRes = caseJsonArr.getJSONObject(i);
            if (caseJsonRes == null || caseJsonRes.isEmpty()) continue;
            String n_ajlx = caseJsonRes.getString("n_ajlx");
            String n_slcx = caseJsonRes.getString("n_slcx");
            if ("implement".equals(caseType) && n_ajlx.equals("首次执行")) {
                // 申请执行标的金额(元)
                BigDecimal n_sqzxbdje = StringUtils.isNotBlank(caseJsonRes.getString("n_sqzxbdje")) ? caseJsonRes.getBigDecimal("n_sqzxbdje") : BigDecimal.ZERO;
//                amtRes = amtRes.add(n_sqzxbdje);
                implementAmt = implementAmt.add(n_sqzxbdje);

                JSONArray c_dsrxxJsonArr = Optional.ofNullable(caseJsonRes.getJSONArray("c_dsrxx")).orElse(new JSONArray());
                if (c_dsrxxJsonArr != null && !c_dsrxxJsonArr.isEmpty()) {
                    for (int j = 0; j < c_dsrxxJsonArr.size(); j++) {
                        JSONObject c_dsrxxJson = c_dsrxxJsonArr.getJSONObject(j);
                        if (c_dsrxxJson != null && !c_dsrxxJson.isEmpty()) {
                            String n_ssdw = c_dsrxxJson.getString("n_ssdw");
                            if ("申请执行人".equals(n_ssdw)) {
                                String c_mc = c_dsrxxJson.getString("c_mc");
                                if ("广州市网商小额贷款有限责任公司".equals(c_mc)) {
                                    wsLoanImplAmt = wsLoanImplAmt.add(n_sqzxbdje);
                                    break;
                                } else if ("广州网商融资担保有限公司".equals(c_mc)) {
                                    wsFinGuaImplAmt = wsFinGuaImplAmt.add(n_sqzxbdje);
                                    break;
                                } else if ("广州网商商业保理有限责任公司".equals(c_mc)) {
                                    wsBusGuaImplAmt = wsBusGuaImplAmt.add(n_sqzxbdje);
                                    break;
                                }
                            }

                        }
                    }
                }


            } else if ("一审".equals(n_slcx) || "二审".equals(n_slcx)) {
                // 起诉标的金额(元)
                BigDecimal n_qsbdje = StringUtils.isNotBlank(caseJsonRes.getString("n_qsbdje")) ? caseJsonRes.getBigDecimal("n_qsbdje") : BigDecimal.ZERO;
                litigationAmt = litigationAmt.add(n_qsbdje);

                JSONArray c_dsrxxJsonArr = Optional.ofNullable(caseJsonRes.getJSONArray("c_dsrxx")).orElse(new JSONArray());
                if (c_dsrxxJsonArr != null && !c_dsrxxJsonArr.isEmpty()) {
                    for (int k = 0; k < c_dsrxxJsonArr.size(); k++) {
                        JSONObject c_dsrxxJson = c_dsrxxJsonArr.getJSONObject(k);
                        if (c_dsrxxJson != null && !c_dsrxxJson.isEmpty()) {
                            String n_ssdw = c_dsrxxJson.getString("n_ssdw");
                            if ("原告".equals(n_ssdw) || "上诉人".equals(n_ssdw)) {
                                String c_mc = c_dsrxxJson.getString("c_mc");
                                if ("广州市网商小额贷款有限责任公司".equals(c_mc)) {
                                    wsLoanLitiAmt = wsLoanLitiAmt.add(n_qsbdje);
                                    break;
                                } else if ("广州网商融资担保有限公司".equals(c_mc)) {
                                    wsFinGuaLitiAmt = wsFinGuaLitiAmt.add(n_qsbdje);
                                    break;
                                } else if ("广州网商商业保理有限责任公司".equals(c_mc)) {
                                    wsBusGuaLitiAmt = wsBusGuaLitiAmt.add(n_qsbdje);
                                    break;
                                }
                            }

                        }
                    }
                }


            }
        }
        result.put("implementAmt",implementAmt);
        result.put("litigationAmt",litigationAmt);

        result.put("wsLoanImplAmt",wsLoanImplAmt);
        result.put("wsLoanLitiAmt",wsLoanLitiAmt);
        result.put("wsFinGuaImplAmt",wsFinGuaImplAmt);
        result.put("wsFinGuaLitiAmt",wsFinGuaLitiAmt);
        result.put("wsBusGuaImplAmt",wsBusGuaImplAmt);
        result.put("wsBusGuaLitiAmt",wsBusGuaLitiAmt);
        return result;
    }

    public JSONObject getSourceData(String appNo,String idNo,String name, String model) {
        Date date = new Date();
        LocalDateTime startDate = date.toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDateTime();
        LocalDateTime endDate = startDate.plusYears(5); // 在开始日期上加五年得到结束日期
        // 将日期转换为指定格式
        DateTimeFormatter authedFormatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        String startDateStr = startDate.format(authedFormatter);
        String inquiredAuth = "authed:" + startDateStr + "-" + endDate.format(authedFormatter);

        Map<String,Object> reqMap = new HashMap<>();
        reqMap.put("app_no", appNo);
        reqMap.put("name", name);
        reqMap.put("id", idNo);
        reqMap.put("inquired_auth", inquiredAuth);
//        reqMap.put("phone", "");
        reqMap.put("cardId", idNo);
        reqMap.put("businessType", "offline");
        reqMap.put("systmeName", "手动");
        reqMap.put("businessScenario", "财务");

        // 2、调用dms
        JSONObject resJson = getSfData(reqMap,model);
        return resJson;
    }

    private JSONObject getSfData(Map<String,Object> reqMap,String model) {
        reqMap.put("model", model);
        String json = JSON.toJSONString(reqMap);
        CloseableHttpClient client = HttpClients.createDefault();
        HttpPost post = new HttpPost(dmsUrl + "/SS01");
        String resStr = null;
        try {
            StringEntity s = new StringEntity(json, "UTF-8");
            s.setContentEncoding("UTF-8");
            s.setContentType("application/json");//发送json数据需要设置contentType
            post.setEntity(s);
            HttpResponse res = client.execute(post);
            if(res.getStatusLine().getStatusCode() == HttpStatus.SC_OK){
                resStr = EntityUtils.toString(res.getEntity(), "UTF-8");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        JSONObject jsonData = new JSONObject();
        if (StringUtils.isNotBlank(resStr)) {
            jsonData = JSONObject.parseObject(resStr);
        }
        return jsonData;
    }
}
